﻿using DataAccess;
using DTO;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAO
{
    public class BaoCaoCongNoDAO
    {
        
       public static List<BaoCaoCongNoDTO> baoCaoTheoThang(string thang)
       {
           List<BaoCaoCongNoDTO> list = new List<BaoCaoCongNoDTO>();
           string query = "select dl.MaDaiLy,dl.TenDaiLy,"; 
                  query+=" ISNULL((select sum(px.TongGiaTri) from PHIEUXUAT px where px.MaDaiLy = dl.MaDaiLy and MONTH(px.NgayLapPhieu)='"+thang+"'),0) as NoPhatSinh,";
                  query+=" (dl.NoCuaDaiLy + ISNULL((select sum(pt.SoTienThu) from PHIEUTHU pt where pt.MaDaiLy = dl.MaDaiLy and MONTH(pt.NgayThuTien)='"+thang+"'),0) - ISNULL((select sum(px.TongGiaTri) from PHIEUXUAT px where px.MaDaiLy = dl.MaDaiLy and MONTH(px.NgayLapPhieu)='"+thang+"'),0)) as NoDau,";
                  query+=" dl.NoCuaDaiLy as NoCuoi from DAILY dl";
           DataTable dt = DataConnection.ExecQuery(query);
           
           if (dt.Rows.Count > 0)
           {
               for (int i = 0; i < dt.Rows.Count; i++)
               {
                   DataRow dr = dt.Rows[i];
                   BaoCaoCongNoDTO bc = new BaoCaoCongNoDTO();
                   bc.TenDaiLy = dr["TenDaiLy"].ToString();
                   bc.NoDau = dr["NoDau"].GetHashCode();
                   bc.NoPhatSinh = dr["NoPhatSinh"].GetHashCode();
                   bc.NoCuoi = dr["NoCuoi"].GetHashCode();
                   string ngay = DateTime.Now.ToShortDateString();
                   bc.ThangBaoCao = ngay;
                   bc.STT = i+1;
                   list.Add(bc);
                   bc.MaDaiLy = dr["MaDaiLy"].ToString();
                //   string them = "INSERT INTO BAOCAODOANHSO(MaDaiLy,TenDaiLy,SoPhieuXuat,TongTriGia,ThangBaoCao,TiLe) VALUES('" + bc.MaDaiLy + "','" + bc.TenDaiLy + "','" + bc.SoPhieuXuat + "','" + bc.TongTriGia + "','" + bc.ThangBaoCao + "','" + bc.TiLe + "')";
                  // DataConnection.ExecNonQuery(them);
               }
           }
           return list;
       }
    }
}
